Question 1: Given some sample data, write a program to answer the following: click here to access the required data set

On Shopify, we have exactly 100 sneaker shops, and each of these shops sells only one model of shoe. We want to do some analysis of the average order value (AOV). When we look at orders data over a 30 day window, we naively calculate an AOV of 3145.13. Given that we know these shops are selling sneakers, a relatively affordable item, something seems wrong with our analysis.

import pandas as pd
import numpy as np
import pandas as pd
import seaborn as sns
from scipy import stats
import plotly.express as px
%matplotlib inline

Data import

data = pd.read_excel("C:\\Users\\kottu\\OneDrive\\Documents\\Shopify\\2019 Winter Data Science Intern Challenge Data Set.xlsx")

data.head()

	order_id	shop_id	user_id	order_amount	total_items	payment_method	created_at
0	16	42	607	704000	2000	credit_card	2017-03-07 04:00:00
1	61	42	607	704000	2000	credit_card	2017-03-04 04:00:00
2	521	42	607	704000	2000	credit_card	2017-03-02 04:00:00
3	1105	42	607	704000	2000	credit_card	2017-03-24 04:00:00
4	1363	42	607	704000	2000	credit_card	2017-03-15 04:00:00

data.describe()

	order_id	shop_id	user_id	order_amount	total_items
count	5000.000000	5000.000000	5000.000000	5000.000000	5000.00000
mean	2500.500000	50.078800	849.092400	3145.128000	8.78720
std	1443.520003	29.006118	87.798982	41282.539349	116.32032
min	1.000000	1.000000	607.000000	90.000000	1.00000
25%	1250.750000	24.000000	775.000000	163.000000	1.00000
50%	2500.500000	50.000000	849.000000	284.000000	2.00000
75%	3750.250000	75.000000	925.000000	390.000000	3.00000
max	5000.000000	100.000000	999.000000	704000.000000	2000.00000

EDA

The data have records for 30 day window Data has 5000 rows with 100 shop_ids Mean value for "order_amount" is 3145.128000 There is no missing value as no NaN appeared in the describe results. There are no duplicate rows.

1a) Think about what could be going wrong with our calculation. Think about a better way to evaluate this data.

data['order_amount'].mean()

3145.128

Average Order Value in the dataset is found to be 3145.128. So it appears that the naive calculation of AOV comes from the mean value (average of all the order_amount data) for "order_amount". The aov here is calculated based on the total order amount of each shop, which doesn't give the picture about the average order value of each item. Since each shop sells only one type of item, the computation of average order value would be different.

Calculating aov per shop

(data.order_amount/data.total_items).mean()

387.7428

As per the given question, assuming each of these shops sells only one model of shoe at a reasonable price, we are trying to identify customer average spending for a sneaker. If we consider the naive calculation, then the price appears higher for shoe purchase. This AOV is calculated by averaging across all 100 shops order amount. However, we should calculate the AOV per shop and then average. The actual order value is 387.7428 by computing the order value of each product in the shop by dividing number of items sold and the total order value.

data["aov"] = data.order_amount/data.total_items

data.sort_values(by = ['aov'], ascending=False)

	order_id	shop_id	user_id	order_amount	total_items	payment_method	created_at	aov
2234	2496	78	707	51450	2	cash	2017-03-26 04:38:52.497	25725.0
2216	2453	78	709	51450	2	cash	2017-03-27 11:04:04.363	25725.0
2241	2513	78	935	51450	2	debit	2017-03-18 18:57:13.421	25725.0
4532	3781	78	889	25725	1	cash	2017-03-11 21:14:49.542	25725.0
2361	2819	78	869	51450	2	debit	2017-03-17 06:25:50.921	25725.0
...	...	...	...	...	...	...	...	...
118	246	92	890	360	4	cash	2017-03-18 17:42:28.606	90.0
3849	1844	92	987	90	1	debit	2017-03-06 07:01:09.439	90.0
1817	1280	92	871	180	2	cash	2017-03-26 16:56:18.843	90.0
771	1928	92	960	270	3	debit	2017-03-21 03:44:07.817	90.0
4701	4220	92	747	90	1	credit_card	2017-03-25 20:16:57.851	90.0

5000 rows × 8 columns

Anomaly detection

However, I can also find some discrepancy in the data, for the shop_id 78. Here is the explanation for the same:

fig = px.box(data, y="aov")
fig.show()

This clearly indicates that there is a major outlier in the dataset, which is for the shop_id 78. It is highly impossible that the cost of sneakers can be 25k. There are two ways of looking at this: - There would be a mismatch in the data entry ( Example: Dollars entered as cents) - Different currency being used to specify the cost, instead of dollars. Hence there is a need to remove the outlier value to obtain the most accurate average order value for the rest of the shops

data_filtered = data[data["shop_id"]!=78]

data_filtered.describe()

	order_id	shop_id	user_id	order_amount	total_items	aov
count	4954.000000	4954.000000	4954.000000	4954.000000	4954.000000	4954.000000
mean	2498.990916	49.819540	848.919257	2717.367784	8.851029	152.475575
std	1444.498907	29.014845	87.846007	41155.996469	116.857286	31.260218
min	1.000000	1.000000	607.000000	90.000000	1.000000	90.000000
25%	1248.250000	24.000000	775.000000	163.000000	1.000000	132.000000
50%	2494.500000	50.000000	849.000000	284.000000	2.000000	153.000000
75%	3750.750000	74.000000	925.000000	390.000000	3.000000	168.000000
max	5000.000000	100.000000	999.000000	704000.000000	2000.000000	352.000000

(data_filtered.order_amount/data_filtered.total_items).mean()

152.47557529269278

data_filtered.describe()

	order_id	shop_id	user_id	order_amount	total_items	aov
count	4954.000000	4954.000000	4954.000000	4954.000000	4954.000000	4954.000000
mean	2498.990916	49.819540	848.919257	2717.367784	8.851029	152.475575
std	1444.498907	29.014845	87.846007	41155.996469	116.857286	31.260218
min	1.000000	1.000000	607.000000	90.000000	1.000000	90.000000
25%	1248.250000	24.000000	775.000000	163.000000	1.000000	132.000000
50%	2494.500000	50.000000	849.000000	284.000000	2.000000	153.000000
75%	3750.750000	74.000000	925.000000	390.000000	3.000000	168.000000
max	5000.000000	100.000000	999.000000	704000.000000	2000.000000	352.000000

fig = px.box(data_filtered, y="aov")
fig.show()

In the filtered data, we can see that the data still has an outlier (352) corresponding to the shop_id 42. This doesn't seem to be unrealistic as it can be one of the highly priced sneakers in the list, which is still affordable. Hence we don't remove this from the actual dataset.

Results for the cleaned data

print("Mean: ",data_filtered.aov.mean())
print("Median: ",data_filtered.aov.median())
print("Mode: ",data_filtered.aov.mode()[0])

Mean:  152.47557529269278
Median:  153.0
Mode:  153.0

This shows that all the measures of central tendency gives almost the same results for the clean dataset.

Results for the original data

print("Mean: ",data.aov.mean())
print("Median: ",data.aov.median())
print("Mode: ",data.aov.mode()[0])

Mean:  387.7428
Median:  153.0
Mode:  153.0

CONCLUSION:

Considering the data with anomalies, one of the best ways to evaluate the average order value is by using the mode or the median method, instead of mean , as it gives better accurate results as shown before

1b) What metric would you report for this dataset?

By following the best practices and taking no findings into consideration, we aren't cleaning the data. Here, metric to be used can be Mode or Median, which looks like a more affordable value than the mean of aov

1c) What is its value?

The metric value in the original dataset by following a new approach is $153

Question 2: For this question you’ll need to use SQL. Follow this link to access the data set required for the challenge. Please use queries to answer the following questions. Paste your queries along with your final numerical answers below.

2a) How many orders were shipped by Speedy Express in total?

SELECT count(orderID) FROM orders o inner join shippers s on o.shipperid = s.shipperid where s.ShipperName = "Speedy Express"

ANSWER : 54

2b) What is the last name of the employee with the most orders?

SELECT e.LastName from orders o join employees e on o.employeeID = e.employeeID group by o.employeeID order by count(o.OrderID) desc LIMIT 1

ANSWER : PEACOCK

2c) What product was ordered the most by customers in Germany?

select p.ProductName from OrderDetails od join Orders o on od.OrderID = o.OrderID join Customers c on o.CustomerID = c.CustomerID join Products p on p.ProductID = od.ProductID where c.country = "Germany" group by p.productID order by sum(quantity) desc limit 1

ANSWER : Boston Crab Meat